package maple.thelittleredbook.user.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import maple.thelittleredbook.user.entity.VerificationCode;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 验证码Mapper接口
 */
@Mapper
public interface VerificationCodeMapper extends BaseMapper<VerificationCode> {

    /**
     * 根据邮箱和用途查询有效的验证码（按创建时间倒序）
     * @param email 邮箱
     * @param purpose 用途
     * @param now 当前时间
     * @return 有效的验证码列表
     */
    @Select("SELECT * FROM verification_codes WHERE email = #{email} AND purpose = #{purpose} " +
            "AND expires_at > #{now} AND is_used = false ORDER BY created_at DESC")
    List<VerificationCode> selectValidCodesByEmailAndPurpose(
            @Param("email") String email,
            @Param("purpose") String purpose,
            @Param("now") LocalDateTime now);

    /**
     * 查询指定邮箱在指定时间段内发送的验证码数量（防刷验证）
     * @param email 邮箱
     * @param purpose 用途
     * @param startTime 开始时间
     * @return 验证码数量
     */
    @Select("SELECT COUNT(*) FROM verification_codes WHERE email = #{email} AND purpose = #{purpose} " +
            "AND created_at > #{startTime}")
    int countRecentCodesByEmailAndPurpose(
            @Param("email") String email,
            @Param("purpose") String purpose,
            @Param("startTime") LocalDateTime startTime);

    /**
     * 查询指定邮箱最近发送的验证码
     * @param email 邮箱
     * @param purpose 用途
     * @return 最近的验证码
     */
    @Select("SELECT * FROM verification_codes WHERE email = #{email} AND purpose = #{purpose} " +
            "ORDER BY created_at DESC LIMIT 1")
    VerificationCode selectLatestCodeByEmailAndPurpose(
            @Param("email") String email,
            @Param("purpose") String purpose);

    /**
     * 将验证码标记为已使用
     * @param id 验证码ID
     * @param usedAt 使用时间
     * @return 影响行数
     */
    @Update("UPDATE verification_codes SET is_used = true, used_at = #{usedAt} WHERE id = #{id}")
    int markAsUsed(@Param("id") Long id, @Param("usedAt") LocalDateTime usedAt);

    /**
     * 使一个邮箱的所有相同用途的验证码失效
     * @param email 邮箱
     * @param purpose 用途
     * @return 影响行数
     */
    @Update("UPDATE verification_codes SET is_used = true WHERE email = #{email} AND purpose = #{purpose} AND is_used = false")
    int invalidateAllCodes(@Param("email") String email, @Param("purpose") String purpose);
}